package com.puccampinas.finalproject.core.persistence.dao.sqlserver;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Date;
import java.util.List;

import com.puccampinas.finalproject.core.logs.LogUtils;
import com.puccampinas.finalproject.core.logs.LoggerType;
import com.puccampinas.finalproject.core.persistence.abstracts.ActivitiesDAO;
import com.puccampinas.finalproject.core.persistence.entity.Activity;
import com.puccampinas.finalproject.core.persistence.entity.Partner;
import com.puccampinas.finalproject.core.persistence.entity.Step;
import com.puccampinas.finalproject.core.persistence.entity.enums.ActivityType;
import com.puccampinas.finalproject.core.persistence.entity.enums.StepType;

public class MySQLActivitiesDAO extends ActivitiesDAO{

	@Override
	public Activity find(Partner partner) {
		return null;
	}
	
	/**
	 * ID, post_date, post_content, post_title
	 */
	
	@Override
	public List<Activity> findAll() {

		long start = System.currentTimeMillis();
		List<Activity> queryList = new ArrayList<Activity>();
		LogUtils.info(LoggerType.DAO, "getting all partners");

		try {
			Statement stm = connection.createStatement();
			ResultSet rs = stm.executeQuery(" SELECT atividades.ID, atividades.post_date, atividades.post_content, atividades.post_title, atividades.post_type " +
								        	  " FROM wp_posts AS atividades INNER JOIN wp_users AS usuarios ON atividades.post_author = usuarios.ID " +
								        	  " WHERE (atividades.post_type = 'campanhas' or atividades.post_type = 'gincanas' or " +
								        	  "		atividades.post_type = 'eventos' or atividades.post_type = 'sorteios' ) " +
								        	  " AND atividades.post_status = 'publish' " +
								        	  " AND usuarios.ID in " +
								        	  "	(SELECT ID FROM wp_users as principais " +
								        	  "		INNER JOIN wp_usermeta as adicionais ON principais.ID = adicionais.user_id " +
								        	  "		WHERE principais.user_status = 0 AND (adicionais.meta_key = 'wp_capabilities' and adicionais.meta_value like '%lojista%')" +
								        	  "	);");

			while(rs.next()) {
            	Activity a = new Activity();
            	a.setId(rs.getLong("ID"));
            	a.setCreationDate(rs.getDate("post_date"));
            	a.setDescription(rs.getString("post_content"));
            	a.setName(rs.getString("post_title"));
            	a.setType(getType(rs.getString("post_type")));
            	a.setSteps(new ArrayList<Step>());
            	
            	//obtem os detalhes do parceiro e retorna o usuario
                queryList.add(findDetails(a));
            }
		} catch(SQLException ex) {
		}

        LogUtils.info(LoggerType.DAO, "users" + queryList.size() + " in (" + (System.currentTimeMillis() - start) + ")");

        return queryList;
	}

	@Override
	public List<Activity> findAll(Long partnerId) {
		long start = System.currentTimeMillis();
		List<Activity> queryList = new ArrayList<Activity>();
		LogUtils.info(LoggerType.DAO, "getting all partners");

		try {
	        PreparedStatement pstm = connection.prepareStatement(" SELECT atividades.ID, atividades.post_date, atividades.post_content, atividades.post_title, atividades.post_type " +
													        	  " FROM wp_posts AS atividades INNER JOIN wp_users AS usuarios ON atividades.post_author = usuarios.ID " +
													        	  " WHERE (atividades.post_type = 'campanhas' or atividades.post_type = 'gincanas' or " +
													        	  "		atividades.post_type = 'eventos' or atividades.post_type = 'sorteios' ) " +
													        	  " AND atividades.post_status = 'publish' " +
													        	  " AND usuarios.ID = ? ;");
			pstm.setLong(1, partnerId);
			ResultSet rs = pstm.executeQuery();
	       
			while (rs.next()) {
            	Activity a = new Activity();
            	a.setId(rs.getLong("ID"));
            	a.setCreationDate(rs.getDate("post_date"));
            	a.setDescription(rs.getString("post_content"));
            	a.setName(rs.getString("post_title"));
            	a.setType(getType(rs.getString("post_type")));
            	a.setSteps(new ArrayList<Step>());

            	queryList.add(findDetails(a));
            }
		} catch(SQLException ex) {
			
		}

        LogUtils.info(LoggerType.DAO, "users" + queryList.size() + " in (" + (System.currentTimeMillis() - start) + ")");

        return queryList;
	}
	
	private Activity findDetails(Activity a) throws SQLException {
    	PreparedStatement pstm = connection.prepareStatement("SELECT * FROM wp_postmeta WHERE wp_postmeta.post_id = ? order by meta_key desc");
        pstm.setLong(1, a.getId());
        ResultSet res = pstm.executeQuery();

        while (res.next()) {
        	if (res.getString("meta_key").equals("_thumbnail_id")) {
        		a.setPhoto(getThumbActivity(res.getLong("meta_value")));
        	} else if (res.getString("meta_key").equals("awards")) {
        		a.setPrize(res.getString("meta_value"));
        	} else if (res.getString("meta_key").equals("end_date")) {
        		try{
        			DateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
        			Date date = sdf.parse(res.getString("meta_value"));
        			a.setEndDate(date);
        		}catch(ParseException ex) {
    				try {
    					DateFormat sdf = new SimpleDateFormat("dd/MM/yyyy HH:mm:SS");
						Date date = sdf.parse(res.getString("meta_value"));
						a.setEndDate(date);
					} catch (ParseException e) {
						a.setEndDate(new Date());
					}
        		}
        	} else if (res.getString("meta_key").equals("max_participants")) {
        		try {
        			Integer max = Integer.parseInt(res.getString("meta_value"));
        			a.setNumUsers(max);
        		} catch(NumberFormatException x) {
        			a.setNumUsers(null);
        		}
        	} else if (res.getString("meta_key").equals("start_date")) {
        		try{
        			DateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
        			Date date = sdf.parse(res.getString("meta_value"));
        			a.setStartDate(date);
        		}catch(ParseException ex) {
    				try {
    					DateFormat sdf = new SimpleDateFormat("dd/MM/yyyy HH:mm:SS");
						Date date = sdf.parse(res.getString("meta_value"));
						a.setStartDate(date);
					} catch (ParseException e) {
						a.setStartDate(new Date());
					}
        		}
        	} else if (res.getString("meta_key").equals("tipos_de_etapas")) {
        		a.setPresence(false);
        		if (res.getString("meta_value").equalsIgnoreCase("presencial")) {
        			a.setPresence(true);
        		}
        	}
        }
		
		return a;
	}
	
	private String getThumbActivity(Long id) throws SQLException {
    	PreparedStatement pstm = connection.prepareStatement("SELECT meta_value FROM wp_postmeta WHERE post_id = ? AND meta_key = '_wp_attached_file'");
        pstm.setLong(1, id);
        ResultSet res = pstm.executeQuery();
        
        if (res.next()) {
        	return "http://tcc.renanwilliam.com/wp-content/uploads/" + res.getString("meta_value");
        }
        
        return "";
	}
	
	private ActivityType getType(String tipo) {
		
		if (tipo.equalsIgnoreCase("campanhas")) {
			return ActivityType.CAMPAIGN;
		} else if (tipo.equalsIgnoreCase("gincanas")) {
			return ActivityType.CONTEST;
		} else if (tipo.equalsIgnoreCase("eventos")) {
			return ActivityType.EVENT;
		} else if (tipo.equalsIgnoreCase("sorteios")) {
			return ActivityType.SORTITION;
		}
		
		return ActivityType.CAMPAIGN;
	}
	
	private Integer getOrder(String value) {
		try {
			List<String> values = Arrays.asList(value.split("\\_"));
			if (values.size() == 1) {
				return 0;
			} else if (values.size() == 2) {
				return Integer.parseInt(values.get(1));
			} else if (values.size() > 2) {
				return Integer.parseInt(values.get(2));
			}
		} catch(Exception ex) {
		}
		
		return 0;
	}
	
	private Step getStepByOrder(Integer order, List<Step> list, boolean generate) {
		for (Step s : list) {
			if (s.getOrder() == order) {
				return s;
			}
		}

		if (!generate) {
			return null;
		}

		Step step = new Step();
		step.setOrder(order);
		return step;
	}
	
	@Override
	public List<Activity> getContests() {
		List<Activity> atividades = new ArrayList<Activity>();
		List<Activity> todas = findAll();
		
		for(Activity a : todas) {
			if (a.getType().equals(ActivityType.CONTEST)) {
				atividades.add(a);
			}
		}

		return atividades;
	}
	
	@Override
	public List<Activity> getSortitions() {
		List<Activity> atividades = new ArrayList<Activity>();
		List<Activity> todas = findAll();
		
		for(Activity a : todas) {
			if (a.getType().equals(ActivityType.SORTITION)) {
				atividades.add(a);
			}
		}
		
		return atividades;
	}
	
	@Override
	public List<Activity> getCampaigns() {
		List<Activity> atividades = new ArrayList<Activity>();
		List<Activity> todas = findAll();
		
		for(Activity a : todas) {
			if (a.getType().equals(ActivityType.CAMPAIGN)) {
				atividades.add(a);
			}
		}
		
		return atividades;
	}
	
	@Override
	public List<Activity> getEvents() {
		List<Activity> atividades = new ArrayList<Activity>();
		List<Activity> todas = findAll();
		
		for(Activity a : todas) {
			if (a.getType().equals(ActivityType.EVENT)) {
				atividades.add(a);
			}
		}
		
		return atividades;
	}
	
	@Override
	public List<Step> getSteps(Long atividadeId){
		List<Step> etapas = new ArrayList<Step>();
		
		try {
			PreparedStatement pstm = connection.prepareStatement("SELECT * FROM wp_postmeta WHERE wp_postmeta.post_id = ? and meta_key like 'steps%' order by meta_key desc");
	        pstm.setLong(1, atividadeId);
	        ResultSet res = pstm.executeQuery();
	        while(res.next()) {
				if (res.getString("meta_key").startsWith("steps")) {
					Step stp = getStepByOrder(getOrder(res.getString("meta_key")), etapas, true);
					        		
					if (res.getString("meta_key").startsWith("steps_aswers")) {
						stp.setAnswer(res.getString("meta_value"));
					} else if (res.getString("meta_key").startsWith("steps_type")) {
						stp.setType(StepType.getType(res.getString("meta_value")));
					} else if (res.getString("meta_key").startsWith("steps")) {
						
						stp.setDescription(res.getString("meta_value"));
					}
					
					if (getStepByOrder(stp.getOrder(), etapas, false) == null) {
						etapas.add(stp);
					}
				}
	        }
		} catch(SQLException ex) {
		}
		
		Collections.sort(etapas);
		
		return etapas;
	}
	
	@Override
	public Activity find(Long atividadeId) {
		long start = System.currentTimeMillis();
		 Activity a = new Activity();
		LogUtils.info(LoggerType.DAO, "getting all partners");

		try {
			PreparedStatement pstm = 
							connection.prepareStatement(" SELECT atividades.ID, atividades.post_date, atividades.post_content, atividades.post_title, atividades.post_type " +
											        	  " FROM wp_posts AS atividades INNER JOIN wp_users AS usuarios ON atividades.post_author = usuarios.ID " +
											        	  " WHERE (atividades.post_type = 'campanhas' or atividades.post_type = 'gincanas' or " +
											        	  "		atividades.post_type = 'eventos' or atividades.post_type = 'sorteios' ) " +
											        	  " AND atividades.post_status = 'publish' " +
											        	  " AND atividades.ID = ? limit 1");
	        pstm.setLong(1, atividadeId);
	        ResultSet rs = pstm.executeQuery();
			
	        if(rs.next()) {
               	a.setId(rs.getLong("ID"));
            	a.setCreationDate(rs.getDate("post_date"));
            	a.setDescription(rs.getString("post_content"));
            	a.setName(rs.getString("post_title"));
            	a.setType(getType(rs.getString("post_type")));
            	a.setSteps(new ArrayList<Step>());
            }
		} catch(SQLException ex) {
		}

        LogUtils.info(LoggerType.DAO, "activity: " + a + " in (" + (System.currentTimeMillis() - start) + ")");

        return a;
	}

	@Override
	public Boolean participar(Long userId, Long atividadeId) {
		long start = System.currentTimeMillis();
		LogUtils.info(LoggerType.DAO, "getting all partners");

		try {
			PreparedStatement pstm = 
							connection.prepareStatement("INSERT into participam values (?,?,NOW())");
	        pstm.setLong(1, atividadeId);
	        pstm.setLong(2, userId);
	        Integer result = pstm.executeUpdate();
			
          	return result == 1;
		} catch(SQLException ex) {
		}

       LogUtils.info(LoggerType.DAO, "participa: " + false + " in (" + (System.currentTimeMillis() - start) + ")");

       return false;
	}

	@Override
	public Boolean desistir(Long userId, Long atividadeId) {
		long start = System.currentTimeMillis();
		LogUtils.info(LoggerType.DAO, "getting all partners");

		try {
			PreparedStatement pstm = 
							connection.prepareStatement("DELETE from participam WHERE id_usuario = ? and id_atividade = ?");
	        pstm.setLong(1, userId);
	        pstm.setLong(2, atividadeId);
	        Integer result = pstm.executeUpdate();
			
          	return result == 1;
		} catch(SQLException ex) {
		}

       LogUtils.info(LoggerType.DAO, "participa: " + false + " in (" + (System.currentTimeMillis() - start) + ")");

       return false;
	}

	@Override
	public Boolean participa(Long userId, Long atividadeId) {
		long start = System.currentTimeMillis();
		LogUtils.info(LoggerType.DAO, "getting all partners");

		try {
			PreparedStatement pstm = 
							connection.prepareStatement("select * from participam where id_usuario = ? and id_atividade = ? limit 1");
	        pstm.setLong(1, userId);
	        pstm.setLong(2, atividadeId);
	        ResultSet rs = pstm.executeQuery();
			
          	return rs.next();
		} catch(SQLException ex) {
		}

       LogUtils.info(LoggerType.DAO, "participa: " + false + " in (" + (System.currentTimeMillis() - start) + ")");

       return false;
		
	}
}
